CREATE OR REPLACE FUNCTION create_referredtickets_table() RETURNS VOID AS '
DECLARE 
  curFeatureValues CURSOR FOR 
      SELECT lead.ft_character_value, refer.ft_character_value
        FROM  t_feature_key krefer, t_feature refer 
            , t_feature_key klead, t_feature lead
            , t_document d
        WHERE d.doc_lr_id = refer.ft_entity_id 
            AND d.doc_lr_id = lead.ft_entity_id 
            AND refer.ft_key_id = krefer.fk_id
            AND lead.ft_key_id = klead.fk_id
            AND krefer.fk_string = ''ReferredTicket''
            AND klead.fk_string = ''LeadTicket''
            AND lead.ft_character_value IS NOT NULL
            AND lead.ft_character_value != ''''
        ORDER BY 1;

  i INTEGER = 0;
  vValueCount INTEGER = 0;
  vInsertCount INTEGER = 0;
  vinsertStmt CHARACTER VARYING(4000);
  vinsertStmt2 CHARACTER VARYING(4000);
  vLeadTicket CHARACTER VARYING;
  vReferredTickets CHARACTER VARYING;
  vReferredTicket CHARACTER VARYING;
BEGIN

    IF EXISTS(SELECT tablename FROM pg_tables WHERE LOWER(tablename) = ''referredtickets'' ) THEN
        DROP TABLE referredtickets;
    END IF;
    CREATE TABLE referredtickets (LeadTicket CHARACTER VARYING(12), ReferredTicket CHARACTER VARYING(12) );


    OPEN curFeatureValues;
    LOOP
        FETCH curFeatureValues INTO vLeadTicket, vReferredTickets;
        IF  NOT FOUND THEN
            EXIT;  -- exit loop
        END IF;

        vinsertStmt := ''INSERT INTO referredtickets (LeadTicket, ReferredTicket) VALUES ('';

        vValueCount := char_length(vReferredTickets) - char_length(replace(vReferredTickets, ''|'', '''')) + 1;
        i := 1;
        LOOP
            vReferredTicket := split_part(vReferredTickets, ''|'', i);
            
            vinsertStmt2 := vinsertStmt || quote_literal(vLeadTicket) || '', '' || quote_literal(vReferredTicket) || '');'';
            IF NOT EXISTS(SELECT LeadTicket FROM referredtickets 
                    WHERE leadticket = vLeadTicket
                        AND referredticket = vReferredTicket) 
               AND vLeadTicket != vReferredTicket THEN
            
                --RAISE NOTICE ''%'', vinsertStmt2;
                EXECUTE vinsertStmt2;
                vInsertCount := vInsertCount + 1;
            END IF;
            i := i + 1;
            IF i > vValueCount THEN
                EXIT;
            END IF;
        END LOOP;
    END LOOP;
    CLOSE curFeatureValues;
    RAISE NOTICE ''Records Inserted: %'', vInsertCount;
END;
' LANGUAGE 'plpgsql';


-- SELECT create_referredtickets_table();